﻿#region Namespaces
	
	using System;
	using System.Collections;
	using System.Collections.Generic;
	using System.Linq;
	using System.Text;
	using System.Data;
	using System.Data.SqlClient;
	using System.Diagnostics;
    using NGenORM.Library.Enum;

#endregion

namespace NGenORM.Library.Util
{
	public class GenericDAL : IDisposable
	{

		#region variables
			DataTable workTable; 
			Object NovoValor;
			SqlDbType NovoValorTipo;
			Object OutputValue;
			string NovoParametro;
			string Output;
			int i = 0;
			bool disposedValue = false; //To detect redundant calls
			Hashtable OutputParameters = new Hashtable(); 
		#endregion

		#region objects
			public ConnectionFactory objConnectionFactory {get; set;} 
			SqlConnection connection = new SqlConnection();
		#endregion

		#region attributes
			public string StringSQL { get; set; }
		#endregion

		protected virtual void Dispose(bool disposing) {
			if (!this.disposedValue) {
				if (disposing) {
					// TODO: dispose managed state (managed objects).
				}

				// TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
				// TODO: set large fields to null.
			}

			this.disposedValue = true;

		}

		// This code added by Visual Basic to correctly implement the disposable pattern.
		public void Dispose()  {
			// Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
			Dispose(true);
			GC.SuppressFinalize(this);
		}

		//protected void Finalize() {
		//    //base.Finalize();
		//}

        public GenericDAL() : base() {
            //CreateConnectionFactory(DBServerType);
		}

		private void CreateConnectionFactory(DBServerType DBServerType) {
			if (objConnectionFactory == null) {
                //objConnectionFactory = objConnectionFactory.ConnectionFactoryFactory(DBServerType);
			}
		}

		public object GetParameters(int index) {
			return OutputParameters[index].ToString();
		}

		/// <summary>
		/// Create datatable to store parameters
		/// </summary>
		/// <remarks></remarks>
		protected void CreateParameters() {
			if (workTable == null) {
				workTable = new DataTable();
				workTable.Columns.Add(new DataColumn("Parameter", typeof(string)));
				workTable.Columns.Add(new DataColumn("Value", typeof(object)));
				workTable.Columns.Add(new DataColumn("ValueType", typeof(SqlDbType)));
				workTable.Columns.Add(new DataColumn("Output", typeof(string)));
				workTable.Columns.Add(new DataColumn("OutputValue", typeof(object)));
			}
		}

		/// <summary>
		/// Limpa a datatable criada
		/// </summary>
		/// <remarks></remarks>
		protected void ClearParameters() {
			workTable.Clear();
		}

		/// <summary>
		/// Adiciona um novo parâmetro ao comando
		/// </summary>
		/// <param name="Parametro">Nome do Parâmetro</param>
		/// <param name="Valor">Valor do Parâmetro</param>
		/// <param name="TipoValor">Tipo do Parâmetro</param>
		/// <remarks></remarks>
		public void AddParameter(object Parametro, object Valor, SqlDbType TipoValor) {
			CreateParameters();
			DataRow row = null;
			row = workTable.NewRow();
			row[0] = Parametro;
			row[1] = Valor;
			row[2] = TipoValor;
			row[3] = "Input";
			row[4] = "";
			workTable.Rows.Add(row);
		}

		/// <summary>
		/// Adiciona um novo parâmetro de saída ao comando
		/// </summary>
		/// <param name="Parametro">Nome do Parâmetro</param>
		/// <param name="Valor">Valor do Parâmetro</param>
		/// <param name="TipoValor">Tipo do Parâmetro</param>
		/// <remarks></remarks>
		public void AddOutParameter(object Parametro, object Valor, SqlDbType TipoValor)
		{
			CreateParameters();
			DataRow row = null;
			row = workTable.NewRow();
			row[0] = Parametro;
			row[1] = Valor;
			row[2] = TipoValor;
			row[3] = "Output";
			row[4] = OutputValue;
			workTable.Rows.Add(row);
		}

		/// <summary>
		/// Executa um comando de consulta
		/// </summary>
		/// <param name="CmdType">Command Type - 1 = Text (Default) / 2 = Stored Procedure</param>
		/// <returns>Return a SqlDataReader</returns>
		/// <remarks></remarks>
		public SqlDataReader ExecuteReader(int CmdType = 1)
		{

			SqlCommand cmd = new SqlCommand();
			try
			{
				CreateParameters();
                cmd.Connection = objConnectionFactory.CreateConnection();
				cmd.CommandTimeout = 100;

				switch (CmdType) {
					case 1:
						//DEFAULT - Text
						cmd.CommandType = CommandType.Text;
						break;
					case 2:
						//Stored Procedure
						cmd.CommandType = CommandType.StoredProcedure;
						break;
				}
				cmd.CommandText = StringSQL;
				cmd.Parameters.Clear();
				if (workTable.Rows.Count > 0)
				{
					for (this.i = 0; this.i <= workTable.Rows.Count - 1; this.i++)
					{
						NovoParametro = workTable.Rows[i][0].ToString();
						NovoValor = workTable.Rows[i][1].ToString();
						NovoValorTipo = (SqlDbType)workTable.Rows[i][2];
						Output = workTable.Rows[i][3].ToString();
						OutputValue = workTable.Rows[i][4].ToString();
						if (Output == "Input") {
							cmd.Parameters.Add(new SqlParameter(NovoParametro, NovoValorTipo)).Value = NovoValor;
						} 
						else if (Output == "Output") {
							cmd.Parameters.Add(new SqlParameter(NovoParametro, NovoValorTipo)).Direction = ParameterDirection.Output;
						}
					}
				}
				objConnectionFactory.OpenConnection();
				return cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch (Exception ex)
			{
				throw new Exception(ex.Message.ToString());
			}
			finally
			{
				ClearParameters();
			}

		}

		/// <summary>
		/// Executa um comando Insert/Update/Delete
		/// CmdType = 1 : Stored Procedure ==/==
		/// CmdType = 2 : Text
		/// </summary>
		/// <returns>Return TRUE if command has been executed with sucess and FALSE if not</returns>
		/// <remarks></remarks>
		public bool ExecuteNonQuery(int CmdType = 1) {

			SqlCommand cmd = new SqlCommand();
			objConnectionFactory.CreateConnection();

			cmd.Connection = connection;
			cmd.CommandTimeout = 100;

			switch (CmdType)
			{
				case 1:
					//Stored Procedure
					cmd.CommandType = CommandType.StoredProcedure;
					break;
				case 2:
					//DEFAULT - Text
					cmd.CommandType = CommandType.Text;
					break;
			}

			cmd.CommandText = StringSQL;
			cmd.Parameters.Clear();

			objConnectionFactory.OpenConnection();
			CreateParameters();


			try
			{
				cmd.Parameters.Clear();

				if (workTable.Rows.Count > 0)
				{

					for (this.i = 0; this.i <= workTable.Rows.Count - 1; this.i++)
					{
						NovoParametro = workTable.Rows[i][0].ToString();
						NovoValor = workTable.Rows[i][1];
						NovoValorTipo = (SqlDbType)workTable.Rows[i][2];
						Output = workTable.Rows[i][3].ToString();
						OutputValue = workTable.Rows[i][4];

						if (Output == "Input") {
							cmd.Parameters.Add(new SqlParameter(NovoParametro, NovoValorTipo)).Value = NovoValor;
						}
						else if (Output == "Output") {
							cmd.Parameters.Add(new SqlParameter(NovoParametro, NovoValorTipo)).Direction = ParameterDirection.Output;
						}

					}

				}

				cmd.ExecuteNonQuery();
				int j = 0;
				for (j = 0; j <= cmd.Parameters.Count - 1; j++) {
					if (cmd.Parameters[j].Direction == ParameterDirection.Output) {
						OutputParameters.Add(j, cmd.Parameters[j].Value);
					}
				}

				return true;


			}
			catch (Exception ex)
			{
				throw new Exception("Erro ao Executar." + ex.Message.ToString() + "");
			}
			finally
			{
				objConnectionFactory.CloseConnection();
				CreateParameters();
			}

		}

	}
}
